﻿using FarPoint.Web.Spread;
using FarPoint.Web.Spread.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ControlExplorer.samples.OrderEntry
{
  public partial class Report : System.Web.UI.Page
  {
    DataSet DataSetPubs1 = new DataSet();
    StyleInfo style = new StyleInfo();
    int stylecnt;
    string sqlConString;

    private void Page_Load(System.Object sender, System.EventArgs e)
    {
      //Put user code to initialize the page here

        this.FpSpread1.UseClipboard = false;
        sqlConString = (string)Session["fpdbpath"];

      if (!Page.IsPostBack)
      {
        InitSpread();
        GetData();
      }

      if (this.InIE9PlusCompatView())
      {
        foreach (FarPoint.Web.Spread.FpSpread spread in this.GetSpreads(this))
        {
          FarPoint.Web.Spread.SheetView activeSheet = spread.ActiveSheetView;
          FarPoint.Web.Spread.Border border = activeSheet.DefaultStyle.Border;
          if (border == null || border.IsEmpty)
          {
            activeSheet.DefaultStyle.Border = new FarPoint.Web.Spread.Border(BorderStyle.Solid, activeSheet.GridLineColor, 1);
          }
        }
      }

    }

    private bool InIE9PlusCompatView()
    {
      if (this.Page.Request.Browser.MajorVersion == 7)
      {
        string userAgent = this.Page.Request.UserAgent;
        int index = userAgent.IndexOf("trident/", StringComparison.OrdinalIgnoreCase);
        int index2 = 0;
        if (index >= 0)
        {
          index2 = userAgent.IndexOf(";", index);
        }
        else
        {
          index2 = -1;
        }
        if (index2 > index && index >= 0)
        {
          double tridentVersion = 0;
          if (double.TryParse(userAgent.Substring(index + 8, index2 - (index + 8)), out tridentVersion))
          {
            return tridentVersion >= 5;
          }
        }
      }
      return false;
    }

    private System.Collections.IEnumerable GetSpreads(Control ctrl)
    {
      System.Collections.Generic.List<FarPoint.Web.Spread.FpSpread> list = new System.Collections.Generic.List<FarPoint.Web.Spread.FpSpread>();
      foreach (Control c in ctrl.Controls)
      {
        foreach (FarPoint.Web.Spread.FpSpread spread in GetSpreads(c))
        {
          list.Add(spread);
        }

        if (c is FarPoint.Web.Spread.FpSpread)
        {
          list.Add((FarPoint.Web.Spread.FpSpread)c);
        }
      }

      return list;
    }

    public void GetData()
    {
      string strselect = null;
      string strtemp = null;
      string strstname = null;
      int rowcnt = 0;
      int intSheetCnt = 0;
      double storetotal = 0;


      DbProviderFactory factory = null;
      DbConnection connection1 = DBHelper.CreateConnection(sqlConString, out factory);
      DbCommand dbCmd = connection1.CreateCommand();
      dbCmd.CommandText = "Select * from Stores";
      connection1.Open();
      DbDataReader objDataReader = dbCmd.ExecuteReader();


      strtemp = "";
      rowcnt = 0;
      intSheetCnt = 0;
      stylecnt = 0;
      while (objDataReader.Read())
      {
        rowcnt = 0;
        storetotal = 0;

        DbConnection connection2 = DBHelper.CreateConnection(sqlConString, out factory);
        DbCommand dbCmd2 = connection2.CreateCommand();
        connection2.Open();

        strselect = "SELECT sales.*, titles.title,titles.type,titles.price FROM sales INNER JOIN titles ON sales.title_id = titles.title_id  where stor_id=" + objDataReader["stor_id"];
        dbCmd2.CommandText = strselect;
        DbDataReader objDataReader1 = dbCmd2.ExecuteReader();

        if (intSheetCnt > 0)
        {
          //FpSpread1.Sheets.Add("Sheet" & intSheetCnt)
          FpSpread1.Sheets.Count = FpSpread1.Sheets.Count + 1;
        }
        DefaultSheetDataModel model = new DefaultSheetDataModel(25, objDataReader1.FieldCount);
        //rows, columns
        FpSpread1.Sheets[intSheetCnt].DataModel = model;
        FpSpread1.Sheets[intSheetCnt].GridLines = GridLines.None;
        FpSpread1.Sheets[intSheetCnt].PageSize = 20;
        FpSpread1.Sheets[intSheetCnt].OperationMode = OperationMode.ReadOnly;


        //Set Column widths
        FpSpread1.Sheets[intSheetCnt].SetColumnWidth(0, 100);
        FpSpread1.Sheets[intSheetCnt].SetColumnWidth(1, 75);
        FpSpread1.Sheets[intSheetCnt].SetColumnWidth(2, 320);
        FpSpread1.Sheets[intSheetCnt].SetColumnWidth(3, 75);
        FpSpread1.Sheets[intSheetCnt].SetColumnWidth(4, 75);

        //Store Name
        model.SetValue(rowcnt, 0, "Sales Report for " + objDataReader["stor_name"]);
        //Add cell span for company name
        FpSpread1.Sheets[intSheetCnt].SpanModel.Add(rowcnt, 0, 1, 4);
        //Set the style
        SetStyleStoreName(rowcnt, intSheetCnt);

        rowcnt = rowcnt + 2;

        while (objDataReader1.Read())
        {
          SetColor(rowcnt, intSheetCnt);

          SetStyleOrder(rowcnt, intSheetCnt);
          model.SetValue(rowcnt, 0, "Order #:");
          model.SetValue(rowcnt, 1, objDataReader1["ord_num"]);
          rowcnt = rowcnt + 1;

          SetStyleOrder(rowcnt, intSheetCnt);
          model.SetValue(rowcnt, 0, "Order Date:");
          model.SetValue(rowcnt, 1, Strings.FormatDateTime(objDataReader1["ord_date"]));
          rowcnt = rowcnt + 1;

          SetStyleOrderHeader(rowcnt, intSheetCnt);
          model.SetValue(rowcnt, 1, "Qty");
          model.SetValue(rowcnt, 2, "Title");
          model.SetValue(rowcnt, 3, "Unit Price");
          model.SetValue(rowcnt, 4, "Ext. Price");
          rowcnt = rowcnt + 1;

          SetStyleOrderData(rowcnt, intSheetCnt);
          model.SetValue(rowcnt, 1, objDataReader1["qty"]);
          model.SetValue(rowcnt, 2, objDataReader1["title"]);
          model.SetValue(rowcnt, 3, Strings.FormatCurrency(objDataReader1["price"], 2));
          double total = Convert.ToInt32(objDataReader1["qty"]) * Convert.ToDouble(objDataReader1["price"]);
          model.SetValue(rowcnt, 4, Strings.FormatCurrency(total, 2));
          rowcnt = rowcnt + 2;

          //Store the order total
          storetotal += total;

        }
        SetStyleOrderTotal(rowcnt, intSheetCnt);
        model.SetValue(rowcnt, 3, "Total:");
        model.SetValue(rowcnt, 4, Strings.FormatCurrency(storetotal, 2));
        model.RowCount = rowcnt;
        //            FpSpread1.SheetViews(intSheetCnt).DataModel = model
        strstname = (string)objDataReader["stor_name"];
        if (strstname.Length > 10)
        {
          strstname = strstname.Substring(0, 10) + "..";
        }

        FpSpread1.Sheets[intSheetCnt].SheetName = strstname;


        //Set the Spread Default style
        SetStyleSpread(intSheetCnt);

        intSheetCnt = intSheetCnt + 1;

        dbCmd2.Dispose();
        connection2.Close();
      }

      objDataReader.Close();
      connection1.Close();

      //FpSpread1.Sheets(0).DataModel = model


    }

    public void DataReaderBinding()
    {
      string strselect = null;
      string strtemp = null;

      DbProviderFactory factory = null;
      DbConnection connection3 = DBHelper.CreateConnection(sqlConString,out factory);
      DbCommand objCommand = connection3.CreateCommand();
      connection3.Open();

      //strselect = "Select * from Stores"
      //Dim objCommand As New SqlCommand("Select * from Stores", SqlConnection1)
      //Dim objDataReader As SqlDataReader
      objCommand.CommandText = "Select * from Stores";
      DbDataReader objDataReader = objCommand.ExecuteReader();

      strtemp = "";
      while (objDataReader.Read())
      {
        DbConnection connection4 = DBHelper.CreateConnection(sqlConString, out factory);
        DbCommand objCommand2 = connection4.CreateCommand();
        connection4.Open();

        strselect = "SELECT sales.*, titles.title,titles.type,titles.price FROM sales INNER JOIN titles ON sales.title_id = titles.title_id  where stor_id=" + objDataReader["stor_id"];
        objCommand2.CommandText = strselect;
        DbDataReader objDataReader1 = objCommand2.ExecuteReader();

        strtemp += "<table>";
        strtemp += "<tr><td colspan=4><b>" + objDataReader["stor_name"] + "</b></td></tr>";
        while (objDataReader1.Read())
        {
          strtemp += "<tr><td>Order Number: </td>";
          strtemp += "<td colspan=2>" + objDataReader1["ord_num"] + "</td></tr>";

          strtemp += "<tr><td>Order Date: </td>";
          strtemp += "<td colspan=2>" + objDataReader1["ord_date"] + "</td></tr>";

          strtemp += "<tr><td>&nbsp;</td>";
          strtemp += "<td><i>Qty</i></td>";
          strtemp += "<td><i>Title</i></td>";
          strtemp += "<td><i>Unit Price</i></td></tr>";

          strtemp += "<tr><td>&nbsp;</td>";
          strtemp += "<td>" + objDataReader1["qty"] + "</td>";
          strtemp += "<td>" + objDataReader1["title"] + "</td>";
          strtemp += "<td align=right>" + Strings.FormatCurrency(objDataReader1["price"], 2) + "</td></tr>";
          //Spacer Row
          strtemp += "<tr><td colspan=4>&nbsp;</td></tr>";
          //strtemp += "<tr><td>" & objDataReader1("ord_date") & "</td><tr>"
        }
        strtemp += "</table><br />";

        objCommand2.Dispose();
        connection4.Close();
      }

      objDataReader.Close();
      connection3.Close();

      Response.Write(strtemp);

    }

    //Sub DataSetrelationships()
    //  Dim strResult As String

    //  'Fill dataset using one data adapter
    //  OleConnection1.ConnectionString = "data source=localhost;initial catalog=pubs;integrated security=SSPI;persist security info=False;workstation id=localhost;packet size=4096"
    //  OleSelectCommand1.Connection = OleConnection1
    //  OleSelectCommand1.CommandType = CommandType.Text

    //  OleSelectCommand1.CommandText = "SELECT * from Sales"

    //  'Fill the dataset
    //  OleDataAdapter1.SelectCommand = OleSelectCommand1
    //  OleDataAdapter1.Fill(DataSetPubs1, "Sales")

    //  OleSelectCommand1.CommandText = "Select * from Stores"
    //  OleDataAdapter1.Fill(DataSetPubs1, "Stores")

    //  OleSelectCommand1.CommandText = "Select * from Titles"
    //  OleDataAdapter1.Fill(DataSetPubs1, "Titles")

    //  'Set up the relationships
    //  Dim objRelation As DataRelation
    //  objRelation = New DataRelation("relStores", _
    //                      DataSetPubs1.Tables("Stores").Columns("stor_id"), _
    //                      DataSetPubs1.Tables("Sales").Columns("stor_id"))
    //  DataSetPubs1.Relations.Add(objRelation)

    //  objRelation = New DataRelation("relTitles", _
    //                      DataSetPubs1.Tables("Titles").Columns("title_id"), _
    //                      DataSetPubs1.Tables("Sales").Columns("title_id"))
    //  DataSetPubs1.Relations.Add(objRelation)


    //  'Create a reference to the relationships
    //  'Stores = main table
    //  Dim objTable As DataTable = DataSetPubs1.Tables("Stores")
    //  Dim objStoresRelation As DataRelation = objTable.ChildRelations("relStores")
    //  Dim objTitlesRelation As DataRelation = objTable.ChildRelations("relTitles")


    //  Dim objrow, objchildrow As DataRow
    //  strResult = ""
    //  For Each objrow In objTable.Rows
    //    'Parent Table (Stores)
    //    strResult += "<b>" & objrow("stor_name") & "</b><br />"

    //    Dim colChildRows() As DataRow = objrow.GetChildRows(objStoresRelation)
    //    For Each objchildrow In colChildRows
    //      'Sales table
    //      strResult += objchildrow("ord_num") & "<br />"
    //    Next
    //    strResult += "<br /><br />"

    //    colChildRows = objrow.GetChildRows(objTitlesRelation)
    //    For Each objchildrow In colChildRows
    //      'Titles table
    //      strResult += objchildrow("title") & "<br />"
    //    Next
    //    strResult += "<br /><br />"
    //  Next
    //  Response.Write(strResult)

    //End Sub


    public void SetStyleStoreName(int rowcnt, int sheetnum)
    {
      //Set style for the store name
      style = new StyleInfo();
      style.Reset();
      //Set Colors
      //style.BackColor = Color.Blue
      //style.ForeColor = Color.Aqua
      style.Font.Bold = true;
      style.Font.Size = FontUnit.Point(12);
      //style.Font.Size = FontUnit.XLarge
      FpSpread1.Sheets[sheetnum].SetStyleInfo(rowcnt, 0, style);
    }

    public void SetStyleOrder(int rowcnt, int sheetnum)
    {
      //Set style for the Order Number, Order Date column
      style = new StyleInfo();
      style.Reset();
      //Set Colors
      //style.BackColor = Color.Blue
      // style.ForeColor = Color.Aqua
      style.HorizontalAlign = HorizontalAlign.Right;

      FpSpread1.Sheets[sheetnum].SetStyleInfo(rowcnt, 0, style);
    }


    public void SetStyleOrderTotal(int rowcnt, int sheetnum)
    {
      //Set style for the Order Number, Order Date column
      style = new StyleInfo();
      style.Reset();
      style.HorizontalAlign = HorizontalAlign.Right;
      style.Font.Bold = true;
      style.Font.Size = FontUnit.Point(10);

      FpSpread1.Sheets[sheetnum].SetStyleInfo(rowcnt, 3, style);
      FpSpread1.Sheets[sheetnum].SetStyleInfo(rowcnt, 4, style);
    }

    public void SetStyleOrderData(int rowcnt, int sheetnum)
    {
      int i = 0;
      //Set style for the Qty, Title, Unit Price Data
      style = new StyleInfo();
      style.Reset();
      style.Font.Size = FontUnit.Point(10);

      for (i = 1; i <= 4; i++)
      {
        switch (i)
        {
          case 1:
          case 3:
          case 4:
            style.HorizontalAlign = HorizontalAlign.Right;
            break;
          default:
            style.HorizontalAlign = HorizontalAlign.Left;
            break;
        }
        FpSpread1.Sheets[sheetnum].SetStyleInfo(rowcnt, i, style);
      }
    }


    public void SetStyleOrderHeader(int rowcnt, int sheetnum)
    {
      int i = 0;
      //Set style for the Qty, Title, Unit Price 
      style = new StyleInfo();
      style.Reset();
      //Set Colors
      //style.BackColor = Color.Blue
      // style.ForeColor = Color.Aqua
      style.Font.Size = FontUnit.Point(10);
      style.Font.Italic = true;

      for (i = 1; i <= 4; i++)
      {
        switch (i)
        {
          case 1:
          case 3:
          case 4:
            style.HorizontalAlign = HorizontalAlign.Right;
            break;
          default:
            style.HorizontalAlign = HorizontalAlign.Left;
            break;
        }
        FpSpread1.Sheets[sheetnum].SetStyleInfo(rowcnt, i, style);
      }
    }

    public void SetStyleSpread(int sheetnum)
    {
      //Set style for Spread
      style = new StyleInfo();
      style.Reset();
      style.Font.Size = FontUnit.Point(10);
      style.Font.Name = "Verdana";

      FpSpread1.Sheets[sheetnum].SetStyleInfo(-1, -1, style);
    }

    public void InitSpread()
    {
      var _with1 = FpSpread1;
      _with1.ColumnHeader.Visible = false;
      _with1.RowHeader.Visible = false;
      _with1.HorizontalScrollBarPolicy = ScrollBarPolicy.AsNeeded;
      _with1.VerticalScrollBarPolicy = ScrollBarPolicy.AsNeeded;
    }

    public void SetColor(int rownum, int sheetnum)
    {
      int i = 0;
      style = new StyleInfo();
      style.Reset();
      if (stylecnt == 0)
      {
        style.BackColor = Color.Beige;
        stylecnt = 1;
      }
      else
      {
        style.BackColor = Color.Wheat;
        stylecnt = 0;
      }

      for (i = rownum; i <= rownum + 3; i++)
      {
        //If FpSpread1.Sheets(sheetnum).RowCount <= i Then FpSpread1.Sheets(sheetnum).RowCount = FpSpread1.Sheets(sheetnum).RowCount + 1
        if (FpSpread1.Sheets[sheetnum].RowCount <= i)
          FpSpread1.Sheets[sheetnum].RowCount = i + 4;
        FpSpread1.Sheets[sheetnum].SetStyleInfo(i, -1, style);
      }
    }

    private void Page_PreRender(object sender, System.EventArgs e)
    {
      //Turn on/off headers and grid lines
      if (ckShowSpread.Checked == true)
      {
        //Turn On headers
        FpSpread1.ActiveSheetView.ColumnHeader.Visible = true;
        FpSpread1.ActiveSheetView.RowHeader.Visible = true;
        FpSpread1.ActiveSheetView.GridLines = GridLines.Both;

      }
      else
      {
        //Turn Off headers
        FpSpread1.ActiveSheetView.ColumnHeader.Visible = false;
        FpSpread1.ActiveSheetView.RowHeader.Visible = false;
        FpSpread1.ActiveSheetView.GridLines = GridLines.None;

      }
    }
  }
}